package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;
import net.sf.json.util.JSONUtils;

import org.apache.log4j.Logger;

import com.dudu.model.Signcode;
import com.dudu.model.SmsTemplate;

import core.CoreDao;
import core.db.DBConnectionUtil;

public class SmsDao {
	private static final Logger log =  core.util.Logger.getLogger(SmsDao.class.getSimpleName());
	
	/**
	 * 根据签名编号查询IP白名单
	 * @param dao
	 * @param signcode
	 * @return
	 */
	public List<JSONObject> queryIpBySigncode(CoreDao dao,String signcode) {
		log.info("SmsDao.queryIpBySigncode");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<JSONObject> result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT ip FROM t_sms_ip WHERE signcode=?" );
			//查询数据
			result = dao.serviceJsonSql(con, sql.toString(), new Object[] { signcode });
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 根据appkey查询签名编号
	 * @param dao
	 * @param appkey
	 * @return
	 */
	public String querySigncodeByAppkey(CoreDao dao,String appkey) {
		log.info("SmsDao.querySigncodeByAppkey");
		//数据库连接
		Connection con = null;
		//最后需返回的
		String signcode = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT signcode FROM t_sms_signcode_app WHERE appkey=?" );
			//查询数据
			List<JSONObject> result = dao.serviceJsonSql(con, sql.toString(), new Object[] { appkey });
			if(!JSONUtils.isNull(result) && result.size()>0){
				JSONObject json = result.get(0); 
				if(!JSONUtils.isNull(json)){
					signcode = json.getString("signcode");
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return signcode;
		
	}
	
	/**
	 * 根据签名编号查询签名
	 * @param dao
	 * @param signcode
	 * @return
	 */
	public Signcode querySigncodeBycode(CoreDao dao,String signcode) {
		log.info("SmsDao.signcode");
		//数据库连接
		Connection con = null;
		//最后需返回的
		Signcode result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT * FROM t_sms_signcode WHERE signcode=?" );
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { signcode });
			//将json类型转化为account
			if (list != null && list.size()>0) {
				List<Signcode> items = dao.covertToBean(list, Signcode.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 查询APP和短信模板间的关系
	 * @param dao
	 * @param appkey
	 * @param templateCode
	 * @return
	 */
	public List<JSONObject> queryByAppkeyAndTemplatecode(CoreDao dao, String appkey, String templateCode) {
		log.info("SmsDao.queryByAppkeyAndTemplatecode");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<JSONObject> result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT * FROM t_sms_template_app WHERE appkey=? AND template_code=?" );
			//查询数据
			result = dao.serviceJsonSql(con, sql.toString(), new Object[] { appkey, templateCode });
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	

	public SmsTemplate queryTemplateBycode(CoreDao dao,String template_code) {
		log.info("SmsDao.queryTemplateBycode");
		//数据库连接
		Connection con = null;
		//最后需返回的
		SmsTemplate result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT * FROM t_sms_template WHERE template_code=?" );
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { template_code });
			//将json类型转化为account
			if (list != null && list.size()>0) {
				List<SmsTemplate> items = dao.covertToBean(list, SmsTemplate.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 获取Account信息的条数，数据状态：00启用，01停用
	 * @param dao
	 * @param paramSql
	 * @return
	 *//*
	public int getTotalAcount(CoreDao dao, String paramSql) {
		log.info("AccountDao.getTotalAcount");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}else{
			paramSql="";
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("select count(1) as totalAcount from t_account tac where 1=1");
			sql.append(paramSql);

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalAcount");
			log.info("getTotalAcount="+sql.toString());

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	*//**
	 * 新建Account信息
	 * @param dao
	 * @param account 成功返回0，失败返回-1
	 * @return
	 *//*
	public int insert(CoreDao dao, Account account) {
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_account(identifier,isv_id,app_id,company_id,account,acc_password,acc_name,account_type,acc_status,linker_name,linker_phone,linker_email,note,create_time,key_temp,key_permanent,call_status_url,callback_url,payment_type,category,dead_time,charging_type,create_user,authentication_url,present_money,credit_limit,product_code,custAccount,parent_accountId,parent_accountIdentify," +
					"ivrdigit_url,record_url)");
			sql.append("VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						account.getIdentifier(),
						account.getIsv_id(),
						account.getApp_id(),
						account.getCompany_id(),
						account.getAccount(),
						account.getAcc_password(),
						account.getAcc_name(),
						account.getAccount_type(),
						account.getAcc_status(),
						account.getLinker_name(),
						account.getLinker_phone(),
						account.getLinker_email(),
						account.getNote(),
						account.getKey_temp(),
						account.getKey_permanent(),
						account.getCall_status_url(),
						account.getCallback_url(),
						account.getPayment_type(),
						account.getCategory(),
						account.getDead_time(),
						account.getCharging_type(),
						account.getCreate_user(),
						account.getAuthentication_url(),
						account.getPresent_money(),
						account.getCredit_limit(),
						account.getProduct_code(),
						account.getCustAccount(),
						account.getParent_accountId(),
						account.getParent_accountIdentify(),
						account.getIvrdigit_url(),
						account.getRecord_url()
							});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	*//**
	 * 删除Account
	 * @param dao
	 * @param account 成功返回0，失败返回-1
	 * @return
	 *//*
	public int delete(CoreDao dao, String accountId) {
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer(" delete from t_account where id =");
			sql.append("?");
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { accountId });

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}

	*//**
	 * 编辑Account
	 * @param dao
	 * @param account
	 * @return 成功返回0，失败返回-1
	 *//*
	public int update(CoreDao dao, Account account) {
		//数据库连接
		Connection con = null;
		int result = -1;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写SQL
			StringBuffer sql = new StringBuffer(
					" UPDATE t_account set acc_password=?,acc_name=?,account_type=?,acc_status=?,linker_name=?,linker_phone=?,note=?,create_time=?,key_temp=?,key_permanent=?,call_status_url=?,callback_url=?,payment_type=?,category=?,dead_time=?,charging_type=?,authentication_url=?,present_money=?,credit_limit=?,product_code=?,custAccount=?," +
					"ivrdigit_url=?,record_url=?");
			sql.append(" where id=?");
			//执行sql，更新相应数据
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {account.getAcc_password(),account.getAcc_name(),account.getAccount_type(),account.getAcc_status(),account.getLinker_name(),account.getLinker_phone(),account.getNote(),account.getCreate_time(),account.getKey_temp(),account.getKey_permanent(),account.getCall_status_url(),account.getCallback_url(),
						account.getPayment_type(),account.getCategory(),account.getDead_time(),account.getCharging_type(),account.getAuthentication_url(),account.getPresent_money(),account.getCredit_limit(),account.getProduct_code(),account.getCustAccount(),
						account.getIvrdigit_url(),account.getRecord_url(),account.getId()}
			);

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	

	*//**
	 * 编辑Account
	 * @param dao
	 * @param account
	 * @return 成功返回0，失败返回-1
	 *//*
	public int updateTempkey(CoreDao dao,String account_identifier,String tempkey) {
		//数据库连接
		Connection con = null;
		int result = -1;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写SQL
			StringBuffer sql = new StringBuffer(" UPDATE t_account set key_temp=? where identifier=?");
			//执行sql，更新相应数据
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
						tempkey,account_identifier
					}
			);

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	*//**
	 * 
	 * @param dao
	 * @param account
	 * @return
	 *//*
	public int updateComAccStatusByComId(CoreDao dao, String status,String comId) {
		//数据库连接
		Connection con = null;
		int result = -1;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写SQL
			StringBuffer sql = new StringBuffer(" ");
			sql.append(" update t_account set acc_status=? where company_id=?  ");
			//执行sql，更新相应数据
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
						status,comId
						}
			);

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	*//**
	 * 设置应用账号下的所有组织账号都改变为该到期时间dead_time
	 * 
	 * accountinforeturnform.js
	 * 
	 * @param dao
	 * @param bean
	 * @return
	 *//*
	public int updateDeadtime(CoreDao dao, Account bean) {
		log.info("AccountDao.updateDeadtime");
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer(
					"UPDATE t_account ta SET ta.dead_time=? WHERE ta.parent_accountId=?");
			log.info("AccountDao.updateDeadtime="+sql);
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {bean.getDead_time(), bean.getId()}
			);
			if (list != null && list.size() > 0) {
				list.get(0);
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	*//**
	 * 查询所有账户信息
	 * @param dao
	 * @param pageSql
	 * @param paramSql
	 * @return
	 *//*
	public List<Account> queryAllAccount(CoreDao dao,String pageSql, String paramSql) {
		log.info("AccountDao.queryAllAccount");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<Account> result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tac.id,tac.identifier,tac.isv_id,tac.app_id,tac.company_id,tac.account,tac.acc_password,tac.acc_name,tac.account_type,tac.acc_status,tac.linker_name,tac.linker_phone,tac.note,tac.create_time,tac.key_temp,tac.key_permanent,tac.call_status_url,tac.callback_url,tac.payment_type,tac.category,tac.dead_time,tac.charging_type,tac.authentication_url,tac.credit_limit,tac.present_money,tac.product_code,tac.custAccount,tac.parent_accountId,tac.parent_accountIdentify," +
					"tac.ivrdigit_url,tac.record_url,tac.record_url,");
			sql.append("tis.name isv_name,tco.name company_name,tap.name app_name");
			sql.append(" FROM t_account tac LEFT JOIN t_app tap ON tac.app_id=tap.id LEFT JOIN t_isv tis ON tac.isv_id=tis.id LEFT JOIN t_company tco ON tac.company_id=tco.id");
			//判断如果查询条件不为空，则拼写sql
			if(StringUtils.isNotEmpty(paramSql)){
				sql.append(" WHERE ");
				sql.append(paramSql);
			}
			sql.append(" ORDER BY tac.create_time DESC ");
			sql.append(pageSql);
			log.info("queryAllAccount="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			//将json类型转化为account
			if (list != null) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	*//**
	 * 查询所有应用账户信息，并存入memcached中
	 * @param dao
	 * @param paramSql
	 * @return
	 *//*
	public List<Account> queryAllAccountToCache(CoreDao dao) {
		log.info("AccountDao.queryAllAccountToCache");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<Account> result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tac.id,tac.identifier,tac.isv_id,tac.app_id,tac.company_id,tac.account,tac.acc_password,tac.acc_name,tac.account_type,tac.acc_status,tac.linker_name,tac.linker_phone,tac.note,tac.create_time,tac.key_temp,tac.key_permanent,tac.call_status_url,tac.callback_url,tac.payment_type,tac.category,tac.dead_time,tac.charging_type,tac.authentication_url,tac.credit_limit,tac.present_money,tac.product_code,tac.custAccount,tac.parent_accountId,tac.parent_accountIdentify," +
					"tac.ivrdigit_url,tac.record_url,");
			sql.append("tis.name isv_name,tco.name company_name,tap.name app_name");
			sql.append(" FROM t_account tac LEFT JOIN t_app tap ON tac.app_id=tap.id LEFT JOIN t_isv tis ON tac.isv_id=tis.id LEFT JOIN t_company tco ON tac.company_id=tco.id");
			//判断如果查询条件不为空，则拼写sql
			if(StringUtils.isNotEmpty(paramSql)){
				sql.append(" WHERE ");
				sql.append(paramSql);
			}
			sql.append(" WHERE tac.account_type='app'");
			sql.append(" ORDER BY tac.create_time DESC ");
			log.info("queryAllAccountToCache="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			//将json类型转化为account
			if (list != null) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	*//**
	 * 查询所有组织账户信息，并存入memcached中
	 * @param dao
	 * @return
	 *//*
	public List<Account> queryAllCompanyToCache(CoreDao dao) {
		log.info("AccountDao.queryAllCompanyToCache");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<Account> result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
//			StringBuffer sql = new StringBuffer("SELECT tac.id,tac.identifier,tac.isv_id,tac.app_id,tac.company_id,tac.account,tac.acc_password,tac.acc_name,tac.account_type,tac.acc_status,tac.linker_name,tac.linker_phone,tac.note,tac.create_time,tac.key_temp,tac.key_permanent,tac.call_status_url,tac.callback_url,tac.payment_type,tac.category,tac.dead_time,tac.charging_type,tac.authentication_url,tac.credit_limit,tac.present_money,tac.product_code,tac.custAccount,tac.parent_accountId,tac.parent_accountIdentify,");
//			sql.append("tis.name isv_name,tco.name company_name,tap.name app_name,taa.amount,tag.amount AS gift");
			StringBuffer sql = new StringBuffer("SELECT tac.identifier,tac.key_temp,tac.key_permanent,tac.parent_accountId,tac.parent_accountIdentify,tac.ivrdigit_url,tac.record_url,");
			sql.append(" FROM t_account tac,t_app tap,t_isv tis,t_company tco,t_account_amount taa,t_account_gift tag ");
			sql.append(" WHERE tac.app_id=tap.id AND tac.isv_id=tis.id AND tac.company_id=tco.id AND tac.id=taa.account_id AND tac.id=tag.account_id AND tac.account_type='company'");
			sql.append(" ORDER BY tac.create_time DESC ");
//			sql.append(" limit 0,18010");
			log.info("queryAllCompanyToCache="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			//将json类型转化为account
			if (list != null) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	
	*//**
	 * 查询所有组织账户信息包括账号余额信息
	 * @param dao
	 * @param pageSql
	 * @param paramSql
	 * @return
	 * SELECT tac.id,tac.identifier,tac.isv_id,tac.app_id,tac.company_id,tac.account,tac.acc_password,tac.acc_name,tac.account_type,
	 *	tac.acc_status,tac.linker_name,tac.linker_phone,tac.note,tac.create_time,tac.key_temp,tac.key_permanent,tac.call_status_url,
	 *	tac.callback_url,tac.payment_type,tac.category,tac.dead_time,tac.charging_type,tac.authentication_url,tac.credit_limit,
	 *	tac.present_money,tac.product_code,tac.custAccount,
	 *	tis.name as isv_name,
	 *	tco.name as company_name,
	 *	tap.name as app_name,
	 *	taa.amount,
	 *	tag.amount AS gift 
	 *	FROM t_account tac
	 *	LEFT JOIN t_app tap ON tac.app_id=tap.id
	 *	LEFT JOIN t_isv tis ON tac.isv_id=tis.id
	 *	LEFT JOIN t_company tco ON tac.company_id=tco.id
	 *	LEFT JOIN t_account_amount taa ON tac.id=taa.account_id
	 *	LEFT JOIN t_account_gift tag ON tac.id=tag.account_id  
	 *	WHERE  tac.account_type='company' ORDER BY create_time DESC  limit 0, 35
	 *//*
	public List<Account> queryAllCompanyAccountAmount(CoreDao dao,String pageSql, String paramSql) {
		log.info("AccountDao.queryAllCompanyAccountAmount");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<Account> result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tac.id,tac.identifier,tac.isv_id,tac.app_id,tac.company_id,tac.account,tac.acc_password,tac.acc_name,tac.account_type,tac.acc_status,tac.linker_name,tac.linker_phone,tac.note,tac.create_time,tac.key_temp,tac.key_permanent,tac.call_status_url,tac.callback_url,tac.payment_type,tac.category,tac.dead_time,tac.charging_type,tac.authentication_url,tac.credit_limit,tac.present_money,tac.product_code,tac.custAccount,tac.parent_accountId,tac.parent_accountIdentify," +
					"tac.ivrdigit_url,tac.record_url,");
			sql.append("tis.name isv_name,tco.name company_name,tap.name app_name,taa.amount,tag.amount AS gift");
			sql.append(" FROM t_account tac,t_app tap,t_isv tis,t_company tco,t_account_amount taa,t_account_gift tag ");
			sql.append(" WHERE tac.app_id=tap.id AND tac.isv_id=tis.id AND tac.company_id=tco.id AND tac.id=taa.account_id AND tac.id=tag.account_id AND tac.account_type='company'");
			//判断如果查询条件不为空，则拼写sql
			if(StringUtils.isNotEmpty(paramSql)){
				sql.append(" AND ");
				sql.append(paramSql);
			}
			sql.append(" ORDER BY create_time DESC ");
			sql.append(pageSql);
			log.info("queryAllCompanyAccountAmount="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			//将json类型转化为account
			if (list != null) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	*//**
	 * 根据账号标识查询账号信息
	 * @param dao
	 * @param account_identifier
	 * @return
	 *//*
	public Account queryByAppAccountIdentifier(CoreDao dao,String account_identifier) {
		log.info("AccountDao.queryByAppAccountIdentifier");
		//数据库连接
		Connection con = null;
		//最后需返回的
		Account result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("select * from t_account where identifier=?" );
			log.info("AccountDao.queryByAppAccountIdentifier SQL="+sql+account_identifier);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { account_identifier });
			//将json类型转化为account
			if (list != null && list.size()>0) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	*//**
	 * 根据账户标识和组织标识查询相应的账号
	 * @param dao
	 * @param account_identifier
	 * @param company_identifier
	 * @return
	 *//*
	public Account queryByAccountCompanyIdentifier(CoreDao dao,String account_identifier,String company_identifier) {
		log.info("AccountDao.queryByAccountCompanyIdentifier");
		//数据库连接
		Connection con = null;
		//最后需返回的
		Account result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT ta.* FROM t_account ta LEFT JOIN t_company tc ON ta.company_id=tc.id LEFT JOIN t_app tp ON tp.id=ta.app_id WHERE tp.identify=? AND tc.identify=?" );
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { account_identifier,company_identifier });
			//将json类型转化为account
			if (list != null && list.size()>0) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	public Account queryAppAccountByAppId(CoreDao dao,String appId) {
		log.info("AccountDao.queryAllAccount");
		//数据库连接
		Connection con = null;
		//最后需返回的
		Account result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("select ta.id,ta.identifier,ta.acc_status,ta.key_temp,ta.key_permanent,ta.product_code,ta.custAccount,ta.ivrdigit_url,tac.record_url from t_account ta where ta.app_id=? and ta.account_type='app'" );
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { appId });
			//将json类型转化为account
			if (list != null && list.size()>0) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	public Account queryByCompanyId(CoreDao dao,String com_id) {
		log.info("AccountDao.queryAllAccount");
		//数据库连接
		Connection con = null;
		//最后需返回的
		Account result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("select * from t_account where company_id=?" );
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { com_id });
			//将json类型转化为account
			if (list != null) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	*//**
     * 根据传入的账号，查找是否有相同账号
     * @param dao
     * @param account
     * @return
     *//*
	public String findQuantityByAccount(CoreDao dao, String account) {
		log.info("AccountDao.queryAllAccount");
		//数据库连接
		Connection con = null;
		//最后需返回的
		String accountNum = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("select COUNT(id) countAcc from t_account where account=?" );
			
			//查询数据
			List<Map<String, Object>> list = dao.servcieSql(con, sql.toString(), new Object[] { account });
			if (list != null) {
				Map<String, Object> maps=list.get(0);
				accountNum = maps.get("countAcc")+"";
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return accountNum;
	}
	
	//停用当天到期的测试账户
	public int updateStatusByDeadTime(CoreDao dao) {
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");		
			
			*//**
			 * 2015-9-28修改，增加了传入参数-当前日期
			 * 解决问题：调试账号到期后无法停用
			 *//*
			String date=DateUtil.parseDate(new Date(),DateUtil.PATTERN);
			StringBuffer sql = new StringBuffer(
					" update t_account set acc_status = 0 where dead_time <= ? and acc_status = 1 and category = '" +AccountCategoryEnum.DEBUG.getKey()+ "' ");
			List<JSONObject> list = dao.serviceJsonSql(
					con, sql.toString(), new Object[] {date}
			);
			

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	*//**
	 * 根据app账号的标识，查询其下的组织账号的数量
	 * @param dao
	 * @param accountIdentify
	 * @return
	 *//*
	public int queryCompanyNumByAccountIdentify(CoreDao dao, String accountIdentify) {
		log.info("AccountDao.queryCompanyNumByAccountIdentify");
		//数据库连接
		Connection con = null;
		//最后需返回的
		int accountNum = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT COUNT(tac.id) AS countAcc FROM t_account tac where tac.parent_accountIdentify=?" );
			
			//查询数据
			List<Map<String, Object>> list = dao.servcieSql(con, sql.toString(), new Object[] { accountIdentify });
			if (list != null&&!list.isEmpty()) {
				Map<String, Object> maps=list.get(0);
				accountNum = Integer.parseInt(String.valueOf(maps.get("countAcc")));
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return accountNum;
	}
	*//**
	 * 根据账号ID查询账号信息
	 * @param dao
	 * @param account_id
	 * @return
	 *//*
	public Account queryAccountById(CoreDao dao,String account_id) {
		log.info("AccountDao.queryAccountById");
		//数据库连接
		Connection con = null;
		//最后需返回的
		Account result = null;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("select * from t_account where id=?" );
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { account_id });
			//将json类型转化为account
			if (list != null && list.size()>0) {
				List<Account> items = dao.covertToBean(list, Account.class);
				result = items.get(0);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}*/
}
